package com.syni.mdd.yhd.common.all.dao.jpa;

import com.syni.mdd.yhd.common.all.dao.BaseJPADao;
import com.syni.mdd.yhd.common.all.entity.BmsVideo;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;
import java.util.Set;

public interface BmsVideoDao extends BaseJPADao<BmsVideo,Integer> {
    @Query(value="select bv.id,bv.latitude,bv.longitude from bms_video bv where IF(?1 != '',bv.addr_city=?1 and IF(?2 != '',bv.addr_district=?2,1=1),1=1) and bv.is_delete=?3 and bv.is_release=?4" +
            " and bv.is_off=?5",nativeQuery=true)
    List<Map<String,Object>> findByAddrCityToFrontShow(String addrCity,String addrDistrict,Integer isDelete,
                                                       Integer isRelease,Integer isOff);

    @Query(value="select bv.id from bms_video bv where bv.id not in(?1) and bv.addr_city= ?2 and  bv.is_delete=?3 and bv.is_release=?4" +
            " and bv.is_off=?5",nativeQuery=true)
    List<Map<String,Object>> findByAddrCityNotInIds(Set videoIds,String addrCity,Integer isDelete,Integer isRelease,Integer isOff);

    @Query(value = "select bv.id from bms_video bv where IF(?1 != '',bv.addr_city=?1 and IF(?2 != '',bv.addr_district=?2,1=1),1=1) and bv.is_delete=?2 and bv.is_release=?3 " +
            "and bv.latitude is not null and bv.longitude is not null order by bv.like_times,bv.new_time limit 0,500",nativeQuery = true)
    List<Map<String,Object>> findByNewestAndHottest(String addrCity,String addrDistrict,String isDelete, String isRelease);

    List<BmsVideo> findByIsDeleteAndIsReleaseAndRecommend(Integer isDelete,Integer isRelease,String recommend,Pageable pageable);

    List<BmsVideo> findByIdNotInAndAddrCityAndRecommendAndIsDeleteAndIsRelease(Set videoIds,String addrCity,String recommend,
                                                                               Integer isDelete,Integer isRelease,Pageable pageable);
    List<BmsVideo> findAllByBmsIdAndIsDeleteAndIsRelease(Integer bmsId, Integer isDelete, Integer isRelease, Sort sort);

    BmsVideo findByIdAndIsDelete(Integer videoId,Integer isDelete);

    List<BmsVideo> findByIdIn(Set videoIds);

    /**统计sql*/
    @Query(value = "select count(bv.id) num from bms_video bv where bv.bms_id = ?1 and bv.is_top = 1 and " +
            "bv.is_delete = 0 and bv.is_off = 0",nativeQuery = true)
    Integer countBusinessTopNum(Integer businessId);

    @Query(value = "select count(bv.id) num from bms_video bv where bv.bms_id = ?1 and bv.from_origin = 2 and bv.is_delete = 0",nativeQuery = true)
    Integer countBusinessReleaseNum(Integer businessId);

    @Query(value = "select bv.id from bms_video bv where ((bv.vendor_name like %?1% ) or (bv.video_describe like %?1% )) " +
            "and bv.is_delete = 0 and bv.is_release = 0 and bv.is_off = 0",nativeQuery = true)
    List<Map<String,Object>> getVideoByNameAndDesc(String searchText);


    @Query(value = "select bv.*,round((POWER(MOD(ABS(bv.longitude - ?3),360),2) + POWER(ABS(bv.latitude - ?4),2)) * 1000000,2) AS distance \n" +
            "from bms_video bv where id in (?1) and is_delete = 0 and is_off = 0 and is_release = 0 " +
            "HAVING distance > ?2 and distance < ?3 limit 0,30",nativeQuery = true)
    List<BmsVideo> findLessThanThreeKmVideo(Set videoIds,Double start,Double end,String lnt,String lat);
}
